﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
namespace Sbook.Data
{
    public class ProductsController:SqlDataProvider
    {
        #region[Products_GetById]
        public List<ProductsInfo> Products_GetById(string Id)
        {
            List<Data.ProductsInfo> list = new List<Data.ProductsInfo>();
            using (SqlCommand dbCmd = new SqlCommand("sp_Products_GetById", GetConnection()))
            {
                Data.ProductsInfo obj = new Data.ProductsInfo();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@PId", Id));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.ProductsIDataReader(dr));
                    }
                    dr.Close();
                    //conn.Close();
                }
            }
            return list;
        }
        #endregion
        #region[Products_GetByTop]
        public List<ProductsInfo> Products_GetByTop(string Top, string Where, string Order)
        {
            List<Data.ProductsInfo> list = new List<Data.ProductsInfo>();
            using (SqlCommand dbCmd = new SqlCommand("sp_Products_GetByTop", GetConnection()))
            {
                Data.ProductsInfo obj = new Data.ProductsInfo();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@Top", Top));
                dbCmd.Parameters.Add(new SqlParameter("@Where", Where));
                dbCmd.Parameters.Add(new SqlParameter("@Order", Order));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.ProductsIDataReader(dr));
                    }
                    dr.Close();
                    //conn.Close();
                }
            }
            return list;
        }
        #endregion
        #region[Products_GetByAll]
        public List<ProductsInfo> Products_GetByAll()
        {
            List<Data.ProductsInfo> list = new List<Data.ProductsInfo>();
            using (SqlCommand dbCmd = new SqlCommand("sp_Products_GetByAll", GetConnection()))
            {
                Data.ProductsInfo obj = new Data.ProductsInfo();
                dbCmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.ProductsIDataReader(dr));
                    }
                    dr.Close();
                    //conn.Close();
                }
            }
            return list;
        }
        #endregion
        #region[Products_Paging]
        public List<ProductsInfo> Products_Paging(string CurentPage, string PageSize)
        {
            List<Data.ProductsInfo> list = new List<Data.ProductsInfo>();
            using (SqlCommand dbCmd = new SqlCommand("sp_Products_Paging", GetConnection()))
            {
                Data.ProductsInfo obj = new Data.ProductsInfo();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@CurentPage", CurentPage));
                dbCmd.Parameters.Add(new SqlParameter("@PageSize", PageSize));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.ProductsIDataReader(dr));
                    }
                    dr.Close();
                    //conn.Close();
                }
            }
            return list;
        }

        
        #endregion

        #region
        public List<ProductsInfo> Products_GetByCatId_Paging(string CatId,string CurentPage, string PageSize)
        {
            List<Data.ProductsInfo> list = new List<Data.ProductsInfo>();
            using (SqlCommand dbCmd = new SqlCommand("sp_Products_GetByCatId_Paging", GetConnection()))
            {
                Data.ProductsInfo obj = new Data.ProductsInfo();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@CatId", CatId));
                dbCmd.Parameters.Add(new SqlParameter("@CurentPage", CurentPage));
                dbCmd.Parameters.Add(new SqlParameter("@PageSize", PageSize));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.ProductsIDataReader(dr));
                    }
                    dr.Close();
                    //conn.Close();
                }
            }
            return list;
        }
        #endregion
            
        #region[GetByCatID]
        public List<ProductsInfo> Products_GetByCaId(string CaId)
        {
            List<Data.ProductsInfo> list = new List<Data.ProductsInfo>();
            using (SqlCommand dbCmd = new SqlCommand("sp_Products_GetByCatId", GetConnection()))
            {
                Data.ProductsInfo obj = new Data.ProductsInfo();
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@CatId", CaId));
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(obj.ProductsIDataReader(dr));
                    }
                    dr.Close();
                    //conn.Close();
                }
            }
            return list;
        }
        #endregion
        #region[Products_Insert]
        public bool Products_Insert(ProductsInfo data)
        {
            using (SqlCommand dbCmd = new SqlCommand("sp_Products_Insert", GetConnection()))
            {
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@PName", data.PName));
                dbCmd.Parameters.Add(new SqlParameter("@PCode", data.PCode));
                dbCmd.Parameters.Add(new SqlParameter("@PPrice", data.PPrice));
                dbCmd.Parameters.Add(new SqlParameter("@PSaleOff", data.PSaleOff));
                dbCmd.Parameters.Add(new SqlParameter("@PUId", data.PUId));
                dbCmd.Parameters.Add(new SqlParameter("@CatId", data.CatId));
                dbCmd.Parameters.Add(new SqlParameter("@AId", data.AId));
                dbCmd.Parameters.Add(new SqlParameter("@PActive", data.PActive));
                dbCmd.Parameters.Add(new SqlParameter("@PAuthor", data.PAuthor));
                dbCmd.Parameters.Add(new SqlParameter("@PImg", data.PImg));
                dbCmd.Parameters.Add(new SqlParameter("@PStatus", data.PStatus));
                dbCmd.Parameters.Add(new SqlParameter("@PNumber", data.PNumber));
                dbCmd.Parameters.Add(new SqlParameter("@PImg2", data.PImg2));
                dbCmd.Parameters.Add(new SqlParameter("@PCreateDate", data.PCreateDate));
                dbCmd.ExecuteNonQuery();
            }
            //Clear cache
            System.Web.HttpContext.Current.Cache.Remove("Products");
            return true;
        }
        #endregion
        #region[Products_Update]
        public bool Products_Update(ProductsInfo data)
        {
            using (SqlCommand dbCmd = new SqlCommand("sp_Products_Update", GetConnection()))
            {
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@PId", data.PId));
                dbCmd.Parameters.Add(new SqlParameter("@PName", data.PName));
                dbCmd.Parameters.Add(new SqlParameter("@PCode", data.PCode));
                dbCmd.Parameters.Add(new SqlParameter("@PPrice", data.PPrice));
                dbCmd.Parameters.Add(new SqlParameter("@PSaleOff", data.PSaleOff));
                dbCmd.Parameters.Add(new SqlParameter("@PUId", data.PUId));
                dbCmd.Parameters.Add(new SqlParameter("@CatId", data.CatId));
                dbCmd.Parameters.Add(new SqlParameter("@AId", data.AId));
                dbCmd.Parameters.Add(new SqlParameter("@PActive", data.PActive));
                dbCmd.Parameters.Add(new SqlParameter("@PAuthor", data.PAuthor));
                dbCmd.Parameters.Add(new SqlParameter("@PImg", data.PImg));
                dbCmd.Parameters.Add(new SqlParameter("@PStatus", data.PStatus));
                dbCmd.Parameters.Add(new SqlParameter("@PNumber", data.PNumber));
                dbCmd.Parameters.Add(new SqlParameter("@PImg2", data.PImg2));
                dbCmd.Parameters.Add(new SqlParameter("@PCreateDate", data.PCreateDate));
                dbCmd.ExecuteNonQuery();
            }
            //Clear cache
            System.Web.HttpContext.Current.Cache.Remove("Products");
            return true;
        }
        #endregion
        #region[Products_Delete]
        public bool Products_Delete(string Id)
        {
            using (SqlCommand dbCmd = new SqlCommand("sp_Products_Delete", GetConnection()))
            {
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.Add(new SqlParameter("@PId", Id));
                dbCmd.ExecuteNonQuery();
            }
            //Clear cache
            System.Web.HttpContext.Current.Cache.Remove("Products");
            return true;
        }
        #endregion
        //Fomat PRoduct Price
        public  List<Data.ProductsInfo> FormatPrice(List<Data.ProductsInfo> listInput)
        {

            
            //return  listInput.Select(p => p.PPrice = (Convert.ToDouble(p.PPrice) * Convert.ToDouble(p.PSaleOff)).ToString());
            for (int i = 0; i < listInput.Count; i++)
            {
                listInput[i].PSaleOff =(Convert.ToDouble(listInput[i].PPrice)- Convert.ToDouble( listInput[i].PSaleOff) *Convert.ToDouble( listInput[i].PPrice)).ToString();
            }
            return listInput;
                
        }
        public List<Data.ProductsInfo> FormatPrice1(List<Data.ProductsInfo> listInput)
        {


            //return  listInput.Select(p => p.PPrice = (Convert.ToDouble(p.PPrice) * Convert.ToDouble(p.PSaleOff)).ToString());
            for (int i = 0; i < listInput.Count; i++)
            {
                listInput[i].PPrice = listInput[i].PPrice.Split('.')[0];
            }
            return listInput;

        }
        public List<Data.ProductsInfo> FormatSaleOff(List<Data.ProductsInfo> listInput)
        {
            PublisherController pl=new PublisherController();
            CategoryInfoController cat=new CategoryInfoController();
            for (int i = 0; i < listInput.Count; i++)
            {
                listInput[i].PSaleOff=(Convert.ToDouble(listInput[i].PSaleOff)*Convert.ToDouble(listInput[i].PPrice)).ToString();
                listInput[i].PUId = pl.Publisher_GetById(listInput[i].PUId)[0].PName;
                if (cat.Category_GetById(listInput[i].CatId).Count > 0)
                {
                    listInput[i].CatId = cat.Category_GetById(listInput[i].CatId)[0].cName;
                }
                if (listInput[i].PPrice != "")
                {
                    listInput[i].PPrice = listInput[i].PPrice.Split('.')[0];
                }

                
            }

            return listInput;
        }
    }
}